/*
 * MIT License
 *
 * Copyright (c) 2023-present, tangli
 *
 * Permission is hereby granted, free of charge, to any person obtaining a copy
 * of this software and associated documentation files (the "Software"), to deal
 * in the Software without restriction, including without limitation the rights
 * to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
 * copies of the Software, and to permit persons to whom the Software is
 * furnished to do so, subject to the following conditions:
 *
 * The above copyright notice and this permission notice shall be included in all
 * copies or substantial portions of the Software.
 *
 * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
 * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
 * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
 * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
 * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
 * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
 * SOFTWARE.
 */

package tony.test.mybatis.datascope.pg

import org.junit.jupiter.api.Assertions.assertFalse
import org.junit.jupiter.api.Assertions.assertTrue
import org.junit.jupiter.api.BeforeEach
import org.junit.jupiter.api.DisplayName
import org.junit.jupiter.api.Nested
import org.junit.jupiter.api.Test
import org.springframework.beans.factory.annotation.Autowired
import org.springframework.test.context.ActiveProfiles
import tony.mybatis.datascope.IDataScopeContext
import tony.mybatis.datascope.handler.DefaultScopedDataPermissionHandler
import tony.mybatis.datascope.interceptor.ScopedDataPermissionInterceptor
import tony.mybatis.datascope.model.ScopedColumn
import tony.test.mybatis.datascope.DataScopeBaseTest

/**
 * PostgreSQL特有语法正确性验证测试
 * @author tangli
 * @date 2025/09/21 10:00
 */
@ActiveProfiles("pg")
@DisplayName("PostgreSQL特有语法正确性验证测试")
class DataScopePgTest : DataScopeBaseTest() {

    @Autowired
    private lateinit var dataScopeContext: IDataScopeContext

    private lateinit var interceptor: ScopedDataPermissionInterceptor

    @BeforeEach
    fun setUp() {
        interceptor = ScopedDataPermissionInterceptor(DefaultScopedDataPermissionHandler())
    }

    @Nested
    @DisplayName("PostgreSQL标识符语法验证")
    inner class PgIdentifierTest {

        @Test
        @DisplayName("双引号表名:正确处理双引号")
        fun testDoubleQuoteTableName() {
            val originalSql = """SELECT * FROM "sys_dict" d"""

            val result = dataScopeContext.dataScope()
                .userIdList(listOf("user1"))
                .call {
                    interceptor.parserSingle(originalSql, "test.pg.doublequote.table")
                }

            logger.info("=== PostgreSQL双引号表名测试 ===")
            logger.info("原始SQL: {}", originalSql)
            logger.info("最终SQL: {}", result)

            assertTrue(result.contains("d.creator_id = 'user1'"))
            assertTrue(result.contains("\"sys_dict\""))
            logger.info("双引号表名处理验证完成")
        }

        @Test
        @DisplayName("双引号列名:正确处理双引号列名")
        fun testDoubleQuoteColumnName() {
            val customColumn = ScopedColumn("d", "\"my_user_id\"", "\"my_org_id\"")

            val result = dataScopeContext.dataScope()
                .userIdList(listOf("user1"))
                .scopedColumns(listOf(customColumn))
                .call {
                    interceptor.parserSingle("SELECT * FROM sys_dict d", "test.pg.doublequote.column")
                }

            logger.info("=== PostgreSQL双引号列名测试 ===")
            logger.info("最终SQL: {}", result)

            assertTrue(result.contains("d.\"my_user_id\" = 'user1'"))
            assertFalse(result.contains("creator_id"))
            logger.info("双引号列名处理验证完成")
        }

        @Test
        @DisplayName("模式名.表名:正确处理完整表名")
        fun testSchemaTableName() {
            val originalSql = """SELECT * FROM "public"."sys_dict" d"""

            val result = dataScopeContext.dataScope()
                .userIdList(listOf("user1"))
                .call {
                    interceptor.parserSingle(originalSql, "test.pg.schema.table")
                }

            logger.info("=== PostgreSQL完整表名测试 ===")
            logger.info("原始SQL: {}", originalSql)
            logger.info("最终SQL: {}", result)

            assertTrue(result.contains("d.creator_id = 'user1'"))
            assertTrue(result.contains("\"public\".\"sys_dict\""))
            logger.info("完整表名处理验证完成")
        }

        @Test
        @DisplayName("混合标识符:双引号和普通标识符混合")
        fun testMixedIdentifiers() {
            val originalSql = """SELECT d."dict_name", d.dict_code FROM "sys_dict" d WHERE d."buildIn" = true"""

            val result = dataScopeContext.dataScope()
                .userIdList(listOf("user1"))
                .call {
                    interceptor.parserSingle(originalSql, "test.pg.mixed.identifiers")
                }

            logger.info("=== PostgreSQL混合标识符测试 ===")
            logger.info("原始SQL: {}", originalSql)
            logger.info("最终SQL: {}", result)

            assertTrue(result.contains("d.creator_id = 'user1'"))
            assertTrue(result.contains("d.\"dict_name\""))
            assertTrue(result.contains("d.dict_code"))
            assertTrue(result.contains("d.\"buildIn\" = true"))
            logger.info("混合标识符处理验证完成")
        }
    }

    @Nested
    @DisplayName("PostgreSQL数据类型语法验证")
    inner class PgDataTypeTest {

        @Test
        @DisplayName("UUID类型:正确处理UUID字段和函数")
        fun testUuidDataType() {
            val originalSql = """
                SELECT d.*
                FROM sys_dict d
                WHERE d.dict_uuid = '550e8400-e29b-41d4-a716-446655440000'::uuid
                  AND d.create_uuid = gen_random_uuid()
            """.trimIndent()

            val result = dataScopeContext.dataScope()
                .userIdList(listOf("user1"))
                .call {
                    interceptor.parserSingle(originalSql, "test.pg.uuid.type")
                }

            logger.info("=== PostgreSQL UUID类型测试 ===")
            logger.info("原始SQL: {}", originalSql)
            logger.info("最终SQL: {}", result)

            assertTrue(result.contains("d.creator_id = 'user1'"))
            assertTrue(result.contains("::uuid"))
            assertTrue(result.contains("gen_random_uuid()"))
            logger.info("UUID类型处理验证完成")
        }

        @Test
        @DisplayName("JSONB类型:复杂JSONB查询权限注入")
        fun testJsonbDataType() {
            val originalSql = """
                SELECT d.*
                FROM sys_dict d
                WHERE d.dict_meta::jsonb @> '{"type": "system"}'::jsonb
                  AND d.dict_meta ->'category' = '"config"'
                  AND d.dict_meta ->>'visible' = 'true'
                  AND d.dict_meta #>'{settings,priority}' IS NOT NULL
            """.trimIndent()

            val result = dataScopeContext.dataScope()
                .userIdList(listOf("user1"))
                .call {
                    interceptor.parserSingle(originalSql, "test.pg.jsonb.type")
                }

            logger.info("=== PostgreSQL JSONB类型测试 ===")
            logger.info("原始SQL: {}", originalSql)
            logger.info("最终SQL: {}", result)

            assertTrue(result.contains("d.creator_id = 'user1'"))
            assertTrue(result.contains("::jsonb"))
            assertTrue(result.contains("@>"))
            assertTrue(result.contains("->"))
            assertTrue(result.contains("->>"))
            assertTrue(result.contains("#>"))
            logger.info("JSONB类型处理验证完成")
        }

        @Test
        @DisplayName("数组类型:PostgreSQL数组操作权限注入")
        fun testArrayDataType() {
            val originalSql = """
                SELECT d.*
                FROM sys_dict d
                WHERE d.dict_tags @> ARRAY['important']
                  AND d.dict_codes && ARRAY['system', 'config']
                  AND array_length(d.dict_values, 1) > 0
                  AND 'buildIn' = ANY(d.dict_flags)
            """.trimIndent()

            val result = dataScopeContext.dataScope()
                .userIdList(listOf("user1"))
                .call {
                    interceptor.parserSingle(originalSql, "test.pg.array.type")
                }

            logger.info("=== PostgreSQL数组类型测试 ===")
            logger.info("原始SQL: {}", originalSql)
            logger.info("最终SQL: {}", result)

            assertTrue(result.contains("d.creator_id = 'user1'"))
            assertTrue(result.contains("@>"))
            assertTrue(result.contains("&&"))
            assertTrue(result.contains("ARRAY["))
            assertTrue(result.contains("array_length"))
            assertTrue(result.contains("ANY("))
            logger.info("数组类型处理验证完成")
        }
    }

    @Nested
    @DisplayName("PostgreSQL特有函数和语法验证")
    inner class PgFunctionTest {

        @Test
        @DisplayName("CTE查询:WITH递归查询权限注入")
        fun testCteQuery() {
            val originalSql = """
                WITH RECURSIVE dict_hierarchy AS (
                    SELECT d.*, 0 as level
                    FROM sys_dict d
                    WHERE d.parent_id IS NULL
                    UNION ALL
                    SELECT d.*, dh.level + 1
                    FROM sys_dict d
                    JOIN dict_hierarchy dh ON d.parent_id = dh.dict_id
                )
                SELECT * FROM dict_hierarchy WHERE level <= 3
            """.trimIndent()

            val result = dataScopeContext.dataScope()
                .userIdList(listOf("user1"))
                .call {
                    interceptor.parserSingle(originalSql, "test.pg.cte.recursive")
                }

            logger.info("=== PostgreSQL CTE递归查询测试 ===")
            logger.info("原始SQL: {}", originalSql)
            logger.info("最终SQL: {}", result)

            assertTrue(result.contains("creator_id = 'user1'"))
            assertTrue(result.contains("WITH RECURSIVE"))
            assertTrue(result.contains("UNION ALL"))
            logger.info("CTE递归查询处理验证完成")
        }

        @Test
        @DisplayName("窗口函数:PostgreSQL高级窗口函数")
        fun testPgWindowFunctions() {
            val originalSql = """
                SELECT d.*,
                       ROW_NUMBER() OVER (PARTITION BY d.dict_type_id ORDER BY d.create_time) as rn,
                       DENSE_RANK() OVER (ORDER BY d.sort DESC) as dense_rank,
                       LAG(d.dict_name, 1) OVER (PARTITION BY d.dict_type_id ORDER BY d.create_time) as prev_name,
                       LEAD(d.dict_name, 1) OVER (PARTITION BY d.dict_type_id ORDER BY d.create_time) as next_name,
                       FIRST_VALUE(d.dict_name) OVER (PARTITION BY d.dict_type_id ORDER BY d.create_time
                                                     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as first_name,
                       LAST_VALUE(d.dict_name) OVER (PARTITION BY d.dict_type_id ORDER BY d.create_time
                                                    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) as last_name
                FROM sys_dict d
            """.trimIndent()

            val result = dataScopeContext.dataScope()
                .userIdList(listOf("user1"))
                .call {
                    interceptor.parserSingle(originalSql, "test.pg.window.functions")
                }

            logger.info("=== PostgreSQL窗口函数测试 ===")
            logger.info("原始SQL: {}", originalSql)
            logger.info("最终SQL: {}", result)

            assertTrue(result.contains("d.creator_id = 'user1'"))
            assertTrue(result.contains("ROW_NUMBER()"))
            assertTrue(result.contains("DENSE_RANK()"))
            assertTrue(result.contains("LAG("))
            assertTrue(result.contains("LEAD("))
            assertTrue(result.contains("FIRST_VALUE"))
            assertTrue(result.contains("LAST_VALUE"))
            assertTrue(result.contains("ROWS BETWEEN"))
            logger.info("窗口函数处理验证完成")
        }

        @Test
        @DisplayName("全文搜索:PostgreSQL全文搜索语法")
        fun testFullTextSearch() {
            val originalSql = """
                SELECT d.*,
                       ts_rank(to_tsvector('english', d.dict_name || ' ' || COALESCE(d.remark, '')),
                              plainto_tsquery('english', 'search term')) as rank
                FROM sys_dict d
                WHERE to_tsvector('english', d.dict_name || ' ' || COALESCE(d.remark, ''))
                      @@ plainto_tsquery('english', 'search term')
                ORDER BY rank DESC
            """.trimIndent()

            val result = dataScopeContext.dataScope()
                .userIdList(listOf("user1"))
                .call {
                    interceptor.parserSingle(originalSql, "test.pg.fulltext.search")
                }

            logger.info("=== PostgreSQL全文搜索测试 ===")
            logger.info("原始SQL: {}", originalSql)
            logger.info("最终SQL: {}", result)

            assertTrue(result.contains("d.creator_id = 'user1'"))
            assertTrue(result.contains("to_tsvector"))
            assertTrue(result.contains("plainto_tsquery"))
            assertTrue(result.contains("ts_rank"))
            assertTrue(result.contains("@@"))
            logger.info("全文搜索处理验证完成")
        }

        @Test
        @DisplayName("聚合函数:PostgreSQL特有聚合函数")
        fun testPgAggregateFunctions() {
            val originalSql = """
                SELECT d.dict_type_id,
                       COUNT(*) as dict_count,
                       STRING_AGG(d.dict_name, ', ' ORDER BY d.sort) as dict_names,
                       ARRAY_AGG(d.dict_code ORDER BY d.sort) as dict_codes,
                       JSON_AGG(json_build_object('name', d.dict_name, 'codeSeq', d.dict_code)) as dict_json,
                       JSONB_AGG(jsonb_build_object('name', d.dict_name, 'codeSeq', d.dict_code)) as dict_jsonb,
                       MODE() WITHIN GROUP (ORDER BY d.sort) as mode_sort,
                       PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY d.sort) as median_sort
                FROM sys_dict d
                WHERE d.build_in = true
                GROUP BY d.dict_type_id
                HAVING COUNT(*) > 2
                ORDER BY dict_count DESC
            """.trimIndent()

            val result = dataScopeContext.dataScope()
                .userIdList(listOf("user1"))
                .call {
                    interceptor.parserSingle(originalSql, "test.pg.aggregate.functions")
                }

            logger.info("=== PostgreSQL聚合函数测试 ===")
            logger.info("原始SQL: {}", originalSql)
            logger.info("最终SQL: {}", result)

            assertTrue(result.contains("d.creator_id = 'user1'"))
            assertTrue(result.contains("STRING_AGG"))
            assertTrue(result.contains("ARRAY_AGG"))
            assertTrue(result.contains("JSON_AGG"))
            assertTrue(result.contains("JSONB_AGG"))
            assertTrue(result.contains("json_build_object"))
            assertTrue(result.contains("jsonb_build_object"))
            assertTrue(result.contains("MODE() WITHIN GROUP"))
            assertTrue(result.contains("PERCENTILE_CONT"))
            logger.info("聚合函数处理验证完成")
        }

        @Test
        @DisplayName("时间函数:PostgreSQL日期时间函数")
        fun testPgDateTimeFunctions() {
            val originalSql = """
                SELECT d.*
                FROM sys_dict d
                WHERE d.create_time >= NOW() - INTERVAL '30 days'
                  AND EXTRACT(YEAR FROM d.create_time) = 2025
                  AND EXTRACT(MONTH FROM d.create_time) >= 9
                  AND EXTRACT(DOW FROM d.create_time) NOT IN (0, 6)
                  AND AGE(NOW(), d.create_time) < INTERVAL '1 year'
                  AND d.create_time::date = CURRENT_DATE
                  AND d.create_time AT TIME ZONE 'UTC' > TIMESTAMP '2025-01-01 00:00:00'
            """.trimIndent()

            val result = dataScopeContext.dataScope()
                .userIdList(listOf("user1"))
                .call {
                    interceptor.parserSingle(originalSql, "test.pg.datetime.functions")
                }

            logger.info("=== PostgreSQL时间函数测试 ===")
            logger.info("原始SQL: {}", originalSql)
            logger.info("最终SQL: {}", result)

            assertTrue(result.contains("d.creator_id = 'user1'"))
            assertTrue(result.contains("NOW()"))
            assertTrue(result.contains("INTERVAL"))
            assertTrue(result.contains("EXTRACT("))
            assertTrue(result.contains("AGE("))
            assertTrue(result.contains("CURRENT_DATE"))
            assertTrue(result.contains("AT TIME ZONE"))
            logger.info("时间函数处理验证完成")
        }
    }

    @Nested
    @DisplayName("PostgreSQL字符集和特殊字符验证")
    inner class PgCharsetTest {

        @Test
        @DisplayName("中文字符:正确处理UTF8字符")
        fun testChineseCharacters() {
            val result = dataScopeContext.dataScope()
                .userIdList(listOf("用户1", "用户2", "管理员"))
                .call {
                    interceptor.parserSingle("SELECT * FROM sys_dict d", "test.pg.chinese")
                }

            logger.info("=== PostgreSQL中文字符测试 ===")
            logger.info("最终SQL: {}", result)

            assertTrue(result.contains("d.creator_id IN ('用户1', '用户2', '管理员')"))
            logger.info("中文字符处理验证完成")
        }

        @Test
        @DisplayName("特殊字符转义:正确处理引号等特殊字符")
        fun testSpecialCharacterEscape() {
            val result = dataScopeContext.dataScope()
                .userIdList(listOf("user'1", "user\"2", "user\\3", "user\$4"))
                .call {
                    interceptor.parserSingle("SELECT * FROM sys_dict d", "test.pg.escape")
                }

            logger.info("=== PostgreSQL特殊字符转义测试 ===")
            logger.info("最终SQL: {}", result)

            assertTrue(result.contains("d.creator_id IN"))
            assertTrue(result.contains("user"))
            logger.info("特殊字符转义处理验证完成")
        }

        @Test
        @DisplayName("PostgreSQL关键字:正确处理PostgreSQL保留关键字")
        fun testPgKeywords() {
            val originalSql = """
                SELECT d."order", d."desc", d."limit"
                FROM sys_dict d
                WHERE d."index" > 0
                  AND d."key" IS NOT NULL
                  AND d."user" != 'admin'
                  AND d."group" IN ('system', 'business')
            """.trimIndent()

            val result = dataScopeContext.dataScope()
                .userIdList(listOf("user1"))
                .call {
                    interceptor.parserSingle(originalSql, "test.pg.keywords")
                }

            logger.info("=== PostgreSQL关键字测试 ===")
            logger.info("原始SQL: {}", originalSql)
            logger.info("最终SQL: {}", result)

            assertTrue(result.contains("d.creator_id = 'user1'"))
            assertTrue(result.contains("d.\"order\""))
            assertTrue(result.contains("d.\"desc\""))
            assertTrue(result.contains("d.\"limit\""))
            assertTrue(result.contains("d.\"index\""))
            assertTrue(result.contains("d.\"key\""))
            assertTrue(result.contains("d.\"user\""))
            assertTrue(result.contains("d.\"group\""))
            logger.info("PostgreSQL关键字处理验证完成")
        }

        @Test
        @DisplayName("Unicode字符:正确处理Unicode和特殊字符")
        fun testUnicodeCharacters() {
            val result = dataScopeContext.dataScope()
                .userIdList(listOf("user😀", "admin🚀", "test💯", "αβγδε"))
                .call {
                    interceptor.parserSingle("SELECT * FROM sys_dict d", "test.pg.unicode")
                }

            logger.info("=== PostgreSQL Unicode字符测试 ===")
            logger.info("最终SQL: {}", result)

            assertTrue(result.contains("d.creator_id IN"))
            assertTrue(result.contains("user😀") || result.contains("user"))
            logger.info("Unicode字符处理验证完成")
        }
    }

    @Nested
    @DisplayName("PostgreSQL存储特性验证")
    inner class PgStorageTest {

        @Test
        @DisplayName("PostgreSQL事务:权限在事务中正确工作")
        fun testPgTransaction() {
            val originalSql = "SELECT * FROM sys_dict d WHERE d.build_in = true FOR UPDATE"

            val result = dataScopeContext.dataScope()
                .userIdList(listOf("user1"))
                .call {
                    interceptor.parserSingle(originalSql, "test.pg.transaction")
                }

            logger.info("=== PostgreSQL事务测试 ===")
            logger.info("原始SQL: {}", originalSql)
            logger.info("最终SQL: {}", result)

            assertTrue(result.contains("d.creator_id = 'user1'"))
            assertTrue(result.contains("FOR UPDATE"))
            logger.info("PostgreSQL事务处理验证完成")
        }

        @Test
        @DisplayName("PostgreSQL锁定读取:FOR SHARE语法")
        fun testPgLockingReads() {
            val originalSql = "SELECT * FROM sys_dict d WHERE d.dict_type_id = 'type1' FOR SHARE"

            val result = dataScopeContext.dataScope()
                .userIdList(listOf("user1"))
                .call {
                    interceptor.parserSingle(originalSql, "test.pg.lock.share")
                }

            logger.info("=== PostgreSQL锁定读取测试 ===")
            logger.info("原始SQL: {}", originalSql)
            logger.info("最终SQL: {}", result)

            assertTrue(result.contains("d.creator_id = 'user1'"))
            assertTrue(result.contains("FOR SHARE"))
            logger.info("PostgreSQL锁定读取处理验证完成")
        }

        @Test
        @DisplayName("PostgreSQL UNION查询:UNION语法权限注入")
        fun testPgUnionQuery() {
            val originalSql = """
                SELECT dict_id, dict_name, 'dict' as source_type FROM sys_dict WHERE build_in = 1
                UNION ALL
                SELECT dict_type_id, dict_type_name, 'type' as source_type FROM sys_dict_type WHERE build_in = 1
                ORDER BY dict_name
            """.trimIndent()

            val result = dataScopeContext.dataScope()
                .userIdList(listOf("user1"))
                .call {
                    interceptor.parserSingle(originalSql, "test.pg.union")
                }

            logger.info("=== PostgreSQL UNION查询测试 ===")
            logger.info("原始SQL: {}", originalSql)
            logger.info("最终SQL: {}", result)

            assertTrue(result.contains("creator_id = 'user1'",true))
            assertTrue(result.contains("UNION ALL",true))
            assertTrue(result.contains("'dict' as source_type",true))
            assertTrue(result.contains("'type' as source_type",true))
            logger.info("PostgreSQL UNION查询处理验证完成")
        }
    }

    @Nested
    @DisplayName("PostgreSQL分页语法验证")
    inner class PgPaginationTest {

        @Test
        @DisplayName("LIMIT OFFSET语法:PostgreSQL分页权限注入")
        fun testPgLimitOffset() {
            val originalSql = "SELECT * FROM sys_dict d ORDER BY d.create_time DESC LIMIT 10 OFFSET 20"

            val result = dataScopeContext.dataScope()
                .userIdList(listOf("user1"))
                .call {
                    interceptor.parserSingle(originalSql, "test.pg.limit.offset")
                }

            logger.info("=== PostgreSQL LIMIT OFFSET测试 ===")
            logger.info("原始SQL: {}", originalSql)
            logger.info("最终SQL: {}", result)

            assertTrue(result.contains("d.creator_id = 'user1'"))
            assertTrue(result.contains("LIMIT 10"))
            assertTrue(result.contains("OFFSET 20"))
            logger.info("LIMIT OFFSET处理验证完成")
        }

        @Test
        @DisplayName("FETCH FIRST语法:SQL标准分页语法")
        fun testPgFetchFirst() {
            val originalSql = "SELECT * FROM sys_dict d ORDER BY d.create_time DESC OFFSET 20 ROWS FETCH FIRST 10 ROWS ONLY"

            val result = dataScopeContext.dataScope()
                .userIdList(listOf("user1"))
                .call {
                    interceptor.parserSingle(originalSql, "test.pg.fetch.first")
                }

            logger.info("=== PostgreSQL FETCH FIRST测试 ===")
            logger.info("原始SQL: {}", originalSql)
            logger.info("最终SQL: {}", result)

            assertTrue(result.contains("d.creator_id = 'user1'"))
            assertTrue(result.contains("OFFSET 20 ROWS"))
            assertTrue(result.contains("FETCH FIRST 10 ROWS ONLY"))
            logger.info("FETCH FIRST处理验证完成")
        }
    }

    @Nested
    @DisplayName("PostgreSQL复杂场景验证")
    inner class PgComplexScenarioTest {

        @Test
        @DisplayName("PostgreSQL存储过程调用:CALL语法")
        fun testPgStoredProcedureCall() {
            val originalSql = "CALL get_dict_by_type('system')"

            val result = dataScopeContext.dataScope()
                .userIdList(listOf("user1"))
                .call {
                    try {
                        interceptor.parserSingle(originalSql, "test.pg.procedure")
                    } catch (e: Exception) {
                        logger.info("存储过程调用不被数据权限处理: {}", e.message)
                        originalSql
                    }
                }

            logger.info("=== PostgreSQL存储过程调用测试 ===")
            logger.info("原始SQL: {}", originalSql)
            logger.info("最终SQL: {}", result)

            assertTrue(result.contains("CALL"))
            assertTrue(result.contains("get_dict_by_type"))
            logger.info("PostgreSQL存储过程调用处理验证完成")
        }

        @Test
        @DisplayName("PostgreSQL复合查询:多种语法组合")
        fun testPgComplexCombination() {
            val originalSql = """
                WITH dict_stats AS (
                    SELECT
                        d.dict_type_id,
                        COUNT(*) as total_count,
                        ARRAY_AGG(d.dict_name ORDER BY d.sort) as names,
                        JSON_AGG(json_build_object('id', d.dict_id, 'name', d.dict_name)) as dict_json
                    FROM sys_dict d
                    WHERE d.build_in = true
                      AND d.create_time >= NOW() - INTERVAL '1 year'
                      AND d.dict_meta::jsonb @> '{"visible": true}'::jsonb
                    GROUP BY d.dict_type_id
                    HAVING COUNT(*) > 2
                )
                SELECT
                    ds.dict_type_id,
                    ds.total_count,
                    ds.names[1:3] as top_names,
                    CASE
                        WHEN ds.total_count < 5 THEN 'small'
                        WHEN ds.total_count < 20 THEN 'medium'
                        ELSE 'large'
                    END as size_category,
                    ROW_NUMBER() OVER (ORDER BY ds.total_count DESC) as rank
                FROM dict_stats ds
                ORDER BY ds.total_count DESC, ds.dict_type_id
                LIMIT 50
            """.trimIndent()

            val result = dataScopeContext.dataScope()
                .userIdList(listOf("user1"))
                .orgIdList(listOf("org1"))
                .call {
                    interceptor.parserSingle(originalSql, "test.pg.complex.combination")
                }

            logger.info("=== PostgreSQL复合查询测试 ===")
            logger.info("原始SQL长度: {}", originalSql.length)
            logger.info("最终SQL: {}", result)

            assertTrue(result.contains("d.creator_id = 'user1'") || result.contains("d.creator_org_id = 'org1'"))
            assertTrue(result.contains("WITH"))
            assertTrue(result.contains("ARRAY_AGG"))
            assertTrue(result.contains("JSON_AGG"))
            assertTrue(result.contains("json_build_object"))
            assertTrue(result.contains("::jsonb"))
            assertTrue(result.contains("@>"))
            assertTrue(result.contains("INTERVAL"))
            assertTrue(result.contains("[1:3]"))
            assertTrue(result.contains("ROW_NUMBER()"))
            logger.info("PostgreSQL复合查询处理验证完成")
        }

        @Test
        @DisplayName("PostgreSQL递归查询:复杂递归场景")
        fun testPgRecursiveQuery() {
            val originalSql = """
                WITH RECURSIVE dict_tree AS (
                    -- 根节点
                    SELECT
                        d.dict_id,
                        d.dict_name,
                        d.parent_id,
                        d.sort,
                        0 as level,
                        ARRAY[d.sort] as path,
                        d.dict_name as root_name
                    FROM sys_dict d
                    WHERE d.parent_id IS NULL AND d.build_in = true

                    UNION ALL

                    -- 子节点
                    SELECT
                        d.dict_id,
                        d.dict_name,
                        d.parent_id,
                        d.sort,
                        dt.level + 1,
                        dt.path || d.sort,
                        dt.root_name
                    FROM sys_dict d
                    JOIN dict_tree dt ON d.parent_id = dt.dict_id
                    WHERE d.build_in = true AND dt.level < 5
                )
                SELECT
                    dt.*,
                    REPEAT('  ', dt.level) || dt.dict_name as indented_name,
                    array_to_string(dt.path, ' -> ') as path_string
                FROM dict_tree dt
                ORDER BY dt.path
            """.trimIndent()

            val result = dataScopeContext.dataScope()
                .userIdList(listOf("user1"))
                .call {
                    interceptor.parserSingle(originalSql, "test.pg.recursive.complex")
                }

            logger.info("=== PostgreSQL递归查询测试 ===")
            logger.info("原始SQL长度: {}", originalSql.length)
            logger.info("最终SQL: {}", result)

            assertTrue(result.contains("creator_id = 'user1'"))
            assertTrue(result.contains("WITH RECURSIVE"))
            assertTrue(result.contains("UNION ALL"))
            assertTrue(result.contains("ARRAY["))
            assertTrue(result.contains("REPEAT("))
            assertTrue(result.contains("array_to_string"))
            logger.info("PostgreSQL递归查询处理验证完成")
        }
    }
}
